import os

from openpyxl import load_workbook


def core_work(file_path):
    # 加载 Excel 文件
    workbook = load_workbook(file_path)

    # 选择要操作的工作表
    sheet = workbook.active

    # 获取 E 列的所有单元格
    e_column = sheet['E']

    # 遍历每个单元格并替换换行符
    for cell in e_column:
        if cell.value:
            # 去掉换行
            cell.value = cell.value.replace('\n', ' ')
            cell.value = cell.value.replace('?', '')
            cell.value = cell.value.replace('!', '')

    # 保存修改后的 Excel 文件
    # 删除 "_new"和后缀名
    new_file_path = file_path.replace('_new', '')
    new_file_path = new_file_path.replace('.xlsx', '')

    workbook.save(f'{new_file_path}_new.xlsx')

    # 保存为 SRT 文件
    # 删除第一行
    sheet.delete_rows(1)
    start_time = sheet['A']
    end_time = sheet['B']
    content = sheet['E']
    with open(f'{new_file_path}_new.srt', 'w', encoding='utf-8') as srt_file:
        i = 0
        for cell2 in start_time:
            if cell2.value:
                srt_file.write(f"{i + 1}\n")
                srt_file.write(f"{start_time[i].value} --> {end_time[i].value}\n")
                srt_file.write(f"{content[i].value}\n\n")
                i += 1
